<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class M_fees Extends MY_Model
{
	private $__table ='fees';
	private $CI_instance;
	public function __construct()
	{
		parent::__construct();
		$this->CI_instance =& get_instance();
	}
	
	public function get_all_fees($id ='')
	{
		if($id == '')
		{
			$query = $this->db->where('is_misc_fee',1)->get($this->__table);
		}else{
			$query = $this->db->select(array('fee_id','fee_desc','fee_stat','fee_name','fee_rate','grade_level','fee_new','fee_old','is_other_fee','is_tuition_fee','is_cca_fee','is_misc_fee','is_assess_fee','fee_order','ay_id'))
								->where('fee_id',$id)->get($this->__table);
		}
		if($query->num_rows() > 0)
		{
			$res = $query->result();
			$new_val = array();
			foreach($res as $key => $re):
				if($this->c_sy->id != $re->ay_id)
				{
					$sq = $this->db->where('fee_id',$re->fee_id)
					               ->where('ay_id',$this->c_sy->id)
					               ->get('fee_for_sy');					   
					if($sq->num_rows() >=1)
					{
						$v = $sq->row();
						$re->fee_rate = $v->fee_rate;
						array_push($new_val,$re);
					}else{
						array_push($new_val,$re);
					}
				}else{
					array_push($new_val,$re);
				}
			endforeach;
		
			return $new_val;
		}else{
			return FALSE;
		}
	}
	
	public function get_tuition_fees_by_grade_level($id)
	{
		$sql = 'SELECT fee_id,fee_desc,fee_stat,fee_name,fee_rate,fee_new,fee_old,levels.level_desc FROM fees
				JOIN levels  ON fees.grade_level = levels.level_id
				WHERE is_tuition_fee != 0
				AND levels.level_id = ?
				AND fees.ay_id = ?';
        $query = $this->db->query($sql,array($id,$this->c_sy->id));
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function get_tuition_fees()
	{
		$sql = "SELECT fee_rate,levels.level_id,levels.level_desc FROM fees
				JOIN levels  ON fees.grade_level = levels.level_id
				WHERE is_tuition_fee != 0
				AND fee_stat = 'active'";
        $query = $this->db->query($sql);
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function get_assessment_fees_by_grade_level()
	{
		$sql = 'SELECT fee_id,fee_desc,fee_stat,fee_name,fee_rate,fee_new,fee_old
				FROM fees
				WHERE is_assess_fee = ?
				AND ay_id = ?
				';
        $query = $this->db->query($sql,array('1',$this->c_sy->id));
		return $query->num_rows() >= 1 ? $query->row() : FALSE;
	}
	
	public function fetch_fees($type,$limit,$start)
	{
		
		$limit = intval($limit);
		$start = intval($start);
		
		$sql = 'SELECT f.fee_id,f.fee_desc,f.fee_stat,f.fee_name,f.fee_rate,f.fee_new,f.fee_old,l.level_desc
				FROM fees f
				LEFT JOIN levels l on l.level_id = f.grade_level
				 ';
		if($type == 'misc')
		{
			$sql .= ' where is_misc_fee = 1';
		}elseif($type == 'elec'){
			$sql .= ' where is_elective_fee = 1';
		}elseif($type == 'cca'){
			$sql .= ' where is_cca_fee = 1';
		}elseif($type == 'other'){
			$sql .= ' where is_other_fee = 1';
		}elseif($type == 'tuit'){
			$sql .= ' where is_tuition_fee = 1';
		}elseif($type == 'assess'){
			$sql .= ' where is_assess_fee = 1';
		}
		
		$sql .=' and ay_id = ?';
		$sql .=' ORDER BY f.grade_level, f.fee_order ASC LIMIT '.$start.','.$limit.';';
        $query = $this->db->query($sql,array($this->c_sy->id));
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function count_all_fees($data = FALSE)
	{
		if($data == FALSE)
		{
			
			$sql = 'select count(fee_id) as total from fees where is_tuition_fee = 1 AND ay_id = ?';
			$query = $this->db->query($sql,array($this->c_sy->id));
			return $query->row()->total;
		}else{
			$sql = 'select count(fee_id) as total from fees ';
			
		if($data == 'misc')
		{
			$sql .= ' where is_misc_fee = 1 AND ay_id = ?';
		}elseif($data == 'elec'){
			$sql .= ' where is_elective_fee = 1 AND ay_id = ?';
		}elseif($data == 'cca'){
			$sql .= ' where is_cca_fee = 1 AND ay_id = ?';
		}elseif($data == 'other'){
			$sql .= ' where is_other_fee = 1 AND ay_id = ?';
		}elseif($data == 'tuit'){
			$sql .= ' where is_tuition_fee = 1 AND ay_id = ?';
		}elseif($data == 'assess'){
			$sql .= ' where is_assess_fee = 1 AND ay_id = ?';
		}
			
			$query = $this->db->query($sql,array($this->c_sy->id));
			return $query->row()->total;
		}
	}
	
	public function add_fees($data)
	{
		// pd($data);
		if($this->verify_data($data,2) == FALSE)
		{
			$data['fee_created'] = NOW;
			$this->db->insert($this->__table,$data);
			return $this->db->affected_rows() > 0 ? array('status'=>'true') : array('status'=>'false');
			
		}else{
			return array('status'=>'exist');
		}
		
	}
	
	public function update_fees($data,$id)
	{
		$fsy = "fee_for_sy";
		$data_two['fee_id'] = $id;
		$data_two['ay_id'] = $this->c_sy->id;
		if($this->verify_data($data_two,2,$fsy) == FALSE){
			$data_two['fee_rate'] = $data['fee_rate'];
			$data_two['created_at'] = NOW;
			$this->db->insert('fee_for_sy', $data_two);
			if($this->db->affected_rows() > 0)
			{
				unset($data['fee_rate']);
				if($this->verify_data($data,2) == FALSE)
				{
					$data['fee_updated'] = NOW;
					$this->db->set($data)->where('fee_id',$id)->update($this->__table);
					return $this->db->affected_rows() > 0 ? array('status'=>'true') : array('status'=>'false');
				}else{
					return array('status'=>'true');
				}
			}else{
				return array('status'=>'false');
			}
			die();
		}else{
			$data_two['fee_rate'] = $data['fee_rate'];
			if($this->verify_data($data_two,2,$fsy) == FALSE){
				$data_two['updated_at'] = NOW;
				$this->db->set($data_two)->where('fee_id',$id)->where('ay_id',$this->c_sy->id)->update('fee_for_sy');
				if($this->db->affected_rows() > 0)
				{
					unset($data['fee_rate']);
					if($this->verify_data($data,2) == FALSE)
					{
						$data['fee_updated'] = NOW;
						$this->db->set($data)->where('fee_id',$id)->update($this->__table);
						return $this->db->affected_rows() > 0 ? array('status'=>'true') : array('status'=>'false');
					}else{
						return array('status'=>'true');
					}
				}else{
					return array('status'=>'false');
				}
			}else{
				unset($data['fee_rate']);
				if($this->verify_data($data,2) == FALSE)
				{
					$data['fee_updated'] = NOW;
					$this->db->set($data)->where('fee_id',$id)->update($this->__table);
					return $this->db->affected_rows() > 0 ? array('status'=>'true') : array('status'=>'false');
				}else{
					return array('status' => 'exist');
				}
			}
		}
	}
	
	/* verify_data
	 * @param array
	 * @param int
	*  verifies if data entered by user is existing already in the database
	*  $strict level 1,2
	*  level 1 = if one field has existing data in database returns true else false
	*  level 2 = if all field has existing data in database returns true else false
	*  12/11/2012
	*/
	public function verify_data($data,$strict_level,$new_table=FALSE)
	{		
			if($strict_level == 1)
			{
				if($new_table){
					$query = $this->db->or_where($data)->get($new_table);
				}else{
					$query = $this->db->or_where($data)->get($this->__table);
				}
			}elseif($strict_level == 2){
				if($new_table){
					$query = $this->db->where($data)->get($new_table);
				}else{
					$query = $this->db->where($data)->get($this->__table);
				}
			}
			return $query->num_rows() > 0 ? TRUE : FALSE;
	}
	
	public function get_fee_array($data = FALSE)
	{
		if($data !== FALSE)
		{
			$query = "SELECT fee_id,
							 concat(fee_name,' | PHP',fee_rate) as description 
					  FROM fees f
					  WHERE fee_stat = 'active' ";
			switch($data)
			{
				case  $data == 'cca':
					$query .= " AND is_cca_fee = 1";
				break;
				case  $data == 'misc':
					$query .= " AND is_misc_fee = 1";
				break;
				
				case  $data == 'tuition':
					$query .= " AND is_tuition_fee = 1";
				break;
				
				case  $data == 'others':
					$query .= " AND is_other_fee = 1";
				break;
				
				case  $data == 'assess':
					$query .= " AND is_assess_fee = 1";
				break;
			
				case  $data == 'electives':
					$query .= " AND is_elective_fee = 1";
				break;
			}
			
			$result = $this->db->query($query);
			
			if($result->num_rows() >=1)
			{
				$data = $result->result();
				$level[''] = '-- select uniform --';
				
				foreach($data as $k => $v)
				{
					$level[$v->fee_id] = $v->description;
				}
			
				return $level;
			}
		}else{
				return array(''=>'No Data');
		}
	}
	
	
	public function enable_misc_fee($x)
	{
		$fee_details = $this->db->select()
								->where('fee_id',$x)
								->get('fees');
		if($fee_details->num_rows() >= 1)
		{
			/*original query. only one should be enable
			$fee = $fee_details->row();
			
			$num_of_active_fees = $this->db->select('count(fee_id) as total_active')
										   ->where('is_misc_fee',1)
										   ->where('fee_stat','active')
										   ->where('grade_level',$fee->grade_level)
										   ->get('fees');
		
			$noaf = $num_of_active_fees->num_rows() >= 1 ? $num_of_active_fees->row()->total_active : 0;

			if($noaf >= 1)
			{
				$update['fee_stat'] = 'inactive';
				$this->db->set($update)
						 ->where('is_misc_fee',1)
						 ->where('grade_level',$fee->grade_level)
						 ->update('fees');
						 
				if($this->db->affected_rows() >= 1)
				{
					$update['fee_stat'] = 'active';
					$this->db->set($update)
							 ->where('fee_id',$x)
							 ->update('fees');
				}else{
					log_message('error','Unable to update misc fees to inactive.');
					return FALSE;
				}				
			}else{
				$update['fee_stat'] = 'active';
				$this->db->set($update)->where('fee_id',$x)->update('fees');
			}*/
			$update['fee_stat'] = 'active';
			$this->db->set($update)->where('fee_id',$x)->update('fees');
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}else{
			log_message('error','Fee details was not found. Fee Id '.$x.'');
			return FALSE;
		}	
	}
	
	public function enable_tuit_fee($x)
	{
		$fee_details = $this->db->select()
								->where('fee_id',$x)
								->get('fees');
		if($fee_details->num_rows() >= 1)
		{
			/*original query. only one should be enable.
			$fee = $fee_details->row();
			
			$num_of_active_fees = $this->db->select('count(fee_id) as total_active')
										   ->where('is_tuition_fee',1)
										   ->where('fee_stat','active')
										   ->where('grade_level',$fee->grade_level)
										   ->get('fees');
		
			$noaf = $num_of_active_fees->num_rows() >= 1 ? $num_of_active_fees->row()->total_active : 0;

			if($noaf >= 1)
			{
				$update['fee_stat'] = 'inactive';
				$this->db->set($update)
						 ->where('is_tuition_fee',1)
						 ->where('grade_level',$fee->grade_level)
						 ->update('fees');
						 
				if($this->db->affected_rows() >= 1)
				{
					$update['fee_stat'] = 'active';
					$this->db->set($update)
							 ->where('fee_id',$x)
							 ->update('fees');
				}else{
					log_message('error','Unable to update tuit fees to inactive.');
					return FALSE;
				}				
			}else{
				$update['fee_stat'] = 'active';
				$this->db->set($update)->where('fee_id',$x)->update('fees');
			}*/
			$update['fee_stat'] = 'active';
			$this->db->set($update)->where('fee_id',$x)->update('fees');
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}else{
			log_message('error','Fee details was not found. Fee Id '.$x.'');
			return FALSE;
		}		
	}
	
	public function enable_cca_fee($x)
	{
		$fee_details = $this->db->select()
								->where('fee_id',$x)
								->get('fees');
		if($fee_details->num_rows() >= 1)
		{
			/*original query. only one should be enable.
			$fee = $fee_details->row();
			
			$num_of_active_fees = $this->db->select('count(fee_id) as total_active')
										   ->where('is_cca_fee',1)
										   ->where('fee_stat','active')
										   ->where('grade_level',$fee->grade_level)
										   ->get('fees');
		
			$noaf = $num_of_active_fees->num_rows() >= 1 ? $num_of_active_fees->row()->total_active : 0;

			if($noaf >= 1)
			{
				$update['fee_stat'] = 'inactive';
				$this->db->set($update)
						 ->where('is_cca_fee',1)
						 ->where('grade_level',$fee->grade_level)
						 ->update('fees');
						 
				if($this->db->affected_rows() >= 1)
				{
					$update['fee_stat'] = 'active';
					$this->db->set($update)
							 ->where('fee_id',$x)
							 ->update('fees');
				}else{
					log_message('error','Unable to update cca fees to inactive.');
					return FALSE;
				}				
			}else{
				$update['fee_stat'] = 'active';
				$this->db->set($update)->where('fee_id',$x)->update('fees');
			}*/
			$update['fee_stat'] = 'active';
			$this->db->set($update)->where('fee_id',$x)->update('fees');
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}else{
			log_message('error','Fee details was not found. Fee Id '.$x.'');
			return FALSE;
		}		
	}
	
	public function disable_fee($x)
	{
		$fee_details = $this->db->select()
								->where('fee_id',$x)
								->get('fees');
		if($fee_details->num_rows() >= 1)
		{
			$update['fee_stat'] = 'inactive';
			$this->db->set($update)->where('fee_id',$x)->update('fees');
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}else{
			log_message('error','Fee details was not found. Fee Id '.$x.'');
			return FALSE;
		}		
	}
	
	public function payment_plans($id =FALSE)
	{		
		$query = $this->db->get('payment_modes');

		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	public function payment_plan_values($id =FALSE)
	{		
		$query = $this->db->where('level_id',$id)
						  ->get('payment_mode_values');

		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	public function payment_modes($id =FALSE)
	{		
		$query = $this->db->where('level_id',$id)
						  ->get('payment_modes');

		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	public function get_payment_plan_values($plan_name,$level_id)
	{
		$check = 'SELECT pmv.payment_plan_tuit,
						 pmv.payment_plan_matr
				  FROM payment_mode_values pmv
				  LEFT JOIN payment_modes pm ON pmv.payment_mode_id = pm.id
				  WHERE pm.payment_mode_name = ?
				  AND pmv.level_id = ?
				  AND pmv.ay_id = ?';
					
		$q_check = $this->db->query($check,array($plan_name,$level_id,$this->c_sy->id));
		if($q_check->num_rows() >=1)
		{
			return $q_check->row();
		}else{
			return FALSE;
		}
	}
	
	
	public function update_payment_modes($x,$type = FALSE,$levels)
	{
		if($type !== FALSE AND in_array($type,$levels))
		{

			$count = 0;
			foreach($x as $k=>$v)
			{
				$sql = "UPDATE payment_modes 
					SET setting_value = ?
					WHERE setting_group = '$type'
					AND setting_name = ? ";
		
				$this->db->query($sql,array($v,$k));
				$query_made['queries'][$count] = $this->db->last_query();
				$query_made['result'][$count] = $this->db->affected_rows() >= 1 ? TRUE : NULL;
				$count++;
			}
		
			return in_array(1,array_values($query_made['result'])) ? TRUE : FALSE;
		}else{
			log_message('e','Update payment modes either $type is FALSE or $type not in_array');
			return FALSE;
		}
	}
	
	public function set_student_payment_mode($wazzup_id,$option)
	{
		$this->db->update('enrollments', $option, array("e_id" => $wazzup_id));
		if($this->db->affected_rows() >= 1):
			return TRUE;
		else:
			return FALSE;
		endif;
	}
	
	public function check_if_paid($id = '')
	{
		$sql = 'SELECT spr_id 
				FROM student_payment_records 
				WHERE spr_enrollment_id = ? 
				AND spr_is_deleted = 0
				AND spr_schoolyear_id = ?';
				
		$res = $this->db->query($sql,array($id,$this->c_sy->id));
		return $res->num_rows() >=1 ? TRUE : FALSE;

	}
	
	public function get_total_fees_for_level($level)
	{
		//select total other fees
		$get_total_othr_sql = 'SELECT COALESCE(sum(fee_rate),0) AS total_other
								FROM fees
								WHERE fee_stat ="active" 
								AND is_other_fee = 1
								AND fee_id IN (SELECT lf_fee_id FROM level_fees WHERE lf_level_id = ?)';
		$result_other = $this->db->query($get_total_othr_sql,array($level));
		
		//select total misc fees
		$get_total_misc_sql = 'SELECT COALESCE(sum(fee_rate),0) AS total_misc 
								FROM fees
								WHERE fee_stat ="active" 
								AND is_misc_fee = 1
								AND grade_level = ?';
		$result_misc = $this->db->query($get_total_misc_sql,array($level));
								
		
		
		//select total tuitfees
		$get_total_tuit_sql = 'SELECT COALESCE(sum(fee_rate),0) AS total_tuit
								FROM fees
								WHERE fee_stat ="active" 
								AND is_tuition_fee = 1
								AND grade_level = ?';
		$result_tuit = $this->db->query($get_total_tuit_sql,array($level));
		
		//select total ccafees
		$get_total_tuit_sql = 'SELECT COALESCE(sum(fee_rate),0) AS total_cca
								FROM fees
								WHERE fee_stat ="active" 
								AND is_cca_fee = 1
								AND grade_level = ?';
		$result_cca = $this->db->query($get_total_tuit_sql,array($level));
								
		
		return $result_tuit->row()->total_tuit + $result_misc->row()->total_misc + $result_other->row()->total_other + $result_cca->row()->total_cca;
	}
	
	
	public function get_fees($type)
	{
		$sql = 'SELECT f.fee_id,f.fee_desc,f.fee_stat,f.fee_name,f.fee_rate,l.level_desc,f.ay_id
				FROM fees f
				LEFT JOIN levels l on l.level_id = f.grade_level
				';
		if($type == 'misc')
		{
			$sql .= ' where is_misc_fee = 1';
		}elseif($type == 'elec'){
			$sql .= ' where is_elective_fee = 1';
		}elseif($type == 'cca'){
			$sql .= ' where is_cca_fee = 1';
		}elseif($type == 'other'){
			$sql .= ' where is_other_fee = 1';
		}elseif($type == 'tuit'){
			$sql .= ' where is_tuition_fee = 1';
		}elseif($type == 'assess'){
			$sql .= ' where is_assess_fee = 1';
		}
		
		$sql .=' ORDER BY f.fee_name,f.grade_level ASC';
		
        $query = $this->db->query($sql);
		if( $query->num_rows() >= 1 )
		{	
			$res = $query->result();
			$new_val = array();
			foreach($res as $key => $re):
				if($this->c_sy->id != $re->ay_id)
				{
					$sq = $this->db->where('fee_id',$re->fee_id)
					               ->where('ay_id',$this->c_sy->id)
					               ->get('fee_for_sy');					   
					if($sq->num_rows() >=1)
					{
						$v = $sq->row();
						$re->fee_rate = $v->fee_rate;
						array_push($new_val,$re);
					}else{
						array_push($new_val,$re);
					}
				}else{
					array_push($new_val,$re);
				}
			endforeach;
		
			return $new_val;
		}else{
			return FALSE;
		}
	}
	
	public function get_student_current_payment_mode_and_due($student_id_no)
	{
		$sql = 'SELECT spm.*
				FROM enrollments e
				LEFT JOIN student_payment_modes spm ON spm.spm_student_id = e.e_id
				WHERE e.verified = 1
				AND e.payment_mode != "none"
				AND e.e_archived = 0
				AND spm.spm_value != 0.00
				AND e.e_student_id = ?
				ORDER BY field(month_name,"December","January","February","March","April","May","June","July","August","September","October","November")';
				
		$res = $this->db->query($sql,array($student_id_no));
		
		return $res->num_rows() >=1 ? $res->result() : FALSE;
	}
	
	public function get_foreign_fees()
	{
		$ci =& get_instance();
		//$ci->load->model('M_levels','ml');
		
		//$levels = $ci->ml->get_all_levels();
		
		
		//foreach($levels as $k => $v)
		//{
		//	$data[$k]['fees'] = $this->_get_level_foreign_fees($v->level_id);
		//	$data[$k]['level_desc'] = $v->level_desc;
		//	$data[$k]['level_code'] = $v->level_code;
		//	$data[$k]['level_id'] = $v->level_id;
		//}
		
		//return $data;
		
		$sql = 'SELECT id,fee_name,fee_desc,fee_rate
				FROM foreign_fee';
				
		$res = $this->db->query($sql);

		return $res->num_rows() >=1 ? $res->row() : FALSE;
	}
	
	public function get_op_fees()
	{
		$sq = $this->db->where('is_elective_fee',1)
					   ->where('fee_stat','active')
					   ->get('fees');
					   
					   
		if($sq->num_rows() >=1)
		{
			$data[''] = 'Select Foreign Fees';
			foreach($sq->result() as $k => $v)
			{
				$data[$v->fee_id] = $v->fee_name .' | '.$v->fee_rate;
			}
			
			return $data;
		}else{
		
			return array(''=>'No Optional or Additional Fees Found');
		}
	}
	
	private function _get_level_foreign_fees($id)
	{
		$q = $this->db->where('level_id',$id)->get('foreign_fee');
		return $q-> num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function set_foreign_fees($post)
	{
		$data['fee_name'] = 'Foreign Fee';
		$data['fee_desc'] = 'Foreign Fee';
		$data['fee_rate'] = $post['add_foreign_price'];
		$data['level_id'] = $post['level'];
		$data['gp_id'] = $this->c_gp->gp_id;
		$data['sy_id'] = $this->c_sy->id;
		$data['created'] = NOW;

		$this->db->insert('foreign_fee',$data);
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	public function get_payment_mode_by_category($cat)
	{
		$ci =& get_instance();
		
		$ci->load->model('M_levels','ml');
		$levels = $ci->ml->get_all_levels();
		
		
		// pd($levels);
		if($levels)
		{
			foreach($levels as $k => $v)
			{
				$data[$k]['level_name'] = $v->level_code;
				$data[$k]['level_id'] = $v->level_desc;
				$data[$k]['students'] = $this->_get_students_by_payment_mode_and_grade_level($v->level_id,$cat);
			
			}
			
			return $data;
		}
	}
	
	private function _get_students_by_payment_mode_and_grade_level($level,$cat)
	{
		$q = $this->db->select('e_id,
						   concat(p.firstname," ",p.middlename," ",p.lastname) as fullname,
						   gender',FALSE)
				 ->join('profiles p','e.e_profile_id = p.profile_id','left')
				 ->where('e.e_grade_level',$level)
				 ->where('e.payment_mode',$cat)
				 ->where('e.e_archived',0)
				 ->where('e.verified',1)
				 ->where('e.e_ay_id',$this->c_sy->id)
				 ->get('enrollments e');
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	
	public function get_student_current_payment_mode_and_due_by_enrollment_id($e_id)
	{
		$sql = 'SELECT spm.*
				FROM enrollments e
				LEFT JOIN student_payment_modes spm ON spm.spm_student_id = e.e_id
				WHERE e.verified = 1
				AND e.payment_mode != "none"
				AND e.e_archived = 0
				AND spm.spm_value != 0.00
				AND e.e_id = ?
				ORDER BY field(month_name,"December","January","February","March","April","May","June","July","August","September","October","November")';
				
		$res = $this->db->query($sql,array($e_id));

		return $res->num_rows() >=1 ? $res->result() : FALSE;
	}
	
	public function delete_foreign_fee_from_level_list($x)
	{
		$this->db->where('id',$x)
				 ->where('sy_id',$this->c_sy->id)
				 ->limit(1)
				 ->delete('foreign_fee');
				 
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function get_foreign_fee_from_level_list($x)
	{
		$q = $this->db->where('id',$x)->get('foreign_fee');
		return $q-> num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	public function update_foreign_fees($post)
	{
		$data['fee_name'] = 'Foreign Fee';
		$data['fee_desc'] = 'Foreign Fee';
		$data['fee_rate'] = $post['add_foreign_price'];
		$data['gp_id'] = $this->c_gp->gp_id;
		$data['sy_id'] = $this->c_sy->id;
		$data['created'] = NOW;

		$this->db->set($data)->where('id',$post['fee_id'])->update('foreign_fee');
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	public function get_other_fees()
	{
		$q = $this->db->select('fee_id,fee_name,fee_rate')
					  ->where('is_other_fee',1)
					  ->get('fees');
		
		if($q->num_rows() >= 1)
		{
			$data[''] = '-- Select Items --';
			foreach($q->result() as $k => $v)
			{
				$data[$v->fee_id.'_'.$v->fee_rate] = ucwords($v->fee_name).' / Php '.number_format($v->fee_rate,2,'.',',');
			}
		}
		
		return $q->num_rows() >= 1 ? $data : FALSE;
	}
	
	public function delete_fee($id)
	{
		$this->db->where('fee_id',$id)->limit(1)->delete('fees');
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function get_balance_report($lvl)
	{
		$students = $this->_gbr_get_student_levels($lvl);
		if($students)
		{
			foreach ($students as $sk => $sv) 
			{
				$stud[$sk]['fullname'] = ucwords(strtolower($sv->student_full_name));
				$stud[$sk]['grade_level'] = ucwords(strtolower($sv->grade_level));
				$stud[$sk]['gender'] = $sv->gender;
				$stud[$sk]['remaining_balance'] = $this->_gbr_get_student_balance($sv->enrollment_id,$sv->profile_id);
			}

			// pd($stud);
			return $stud;
		}else{
			return FALSE;
		}
	}
	
	private function _gbr_get_student_levels($lvl = 0)
	{
		//function called by get_balance_report()
		$sql = 'SELECT concat_ws("",p.lastname," , ",p.firstname) as student_full_name,
					   p.gender,
					   l.level_desc as grade_level,
					   e.e_id as enrollment_id,
					   e.e_profile_id as profile_id
				FROM enrollments e
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				LEFT JOIN levels l ON l.level_id = e.e_grade_level
				WHERE (e.moved_to = "yes" OR e.guidance_approved = 1)
				AND e.registrar_approved = 1 
				AND e.e_ay_id = ? ';

		if($lvl == 0){
			$sql .=' ORDER BY p.lastname';
			$res = $this->db->query($sql,[$this->c_sy->id]);
		}else{
			$sql .=' AND e.e_grade_level = ? ORDER BY p.lastname';
			$res = $this->db->query($sql,[$this->c_sy->id,$lvl]);			
		}

		$res = $this->db->query($sql,[$this->c_sy->id,$lvl]);
		return $res->num_rows() >= 1 ? $res->result() : FALSE;
	}
	//param enrollment id and profile id
	private function _gbr_get_student_balance($e_id,$p_id)
	{
		$CI =& get_instance();
		$CI->load->model('M_enrollees');
		
		$sum_amount_due =$CI->M_enrollees->get_sum_of_amount_due($e_id,$this->c_sy->id);
		//$electives_total = $CI->M_enrollees->get_sum_of_student_electives($e_id);
		$sum_paid_fees = $CI->M_enrollees->get_sum_of_paid_fees($e_id,$this->c_sy->id);

		return round(($sum_amount_due) - $sum_paid_fees,2);
	}
}
